package com.kaifamiao.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 使用 JDBC 执行 DML 语句 ( INSERT 、UPDATE 、DELETE )
 * -- MySQL
 * CREATE TABLE t_staff (
 *   id INT auto_increment ,
 *   name VARCHAR(50) ,
 *   gender CHAR(6) ,
 *   hiredate DATE ,
 *   PRIMARY KEY( id )
 * );
 * -- Oracle
 * CREATE TABLE t_staff (
 *   id NUMBER(5) ,
 *   name VARCHAR2(50) ,
 *   gender CHAR(6) ,
 *   hiredate DATE ,
 *   PRIMARY KEY( id )
 * );
 */
public class JdbcTest07 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        // String connect = "mysql" ;
        String connect = "oracle" ;

        String driver = null ;
        String url = null ;
        String username = null ;
        String password = null ;

        switch ( connect ){
            case "mysql":
                driver = "com.mysql.cj.jdbc.Driver" ;
                url = "jdbc:mysql://localhost:3306/kaifamiao?serverTimezone=Asia/Shanghai" ;
                username = "root";
                password = "" ;
                break;
            case "oracle" :
                driver = "oracle.jdbc.OracleDriver" ;
                url = "jdbc:oracle:thin:@localhost:1521:kfm" ;
                username = "kaifamiao";
                password = "kaifamiao" ;
                break ;
        }

        // 加载驱动
        Class.forName( driver ) ; // ClassNotFoundException
        // 建立连接
        Connection conn = DriverManager.getConnection( url , username , password );
        // 创建Statement
        Statement st = conn.createStatement();

        // 事务处理第一步: 先关闭自动提交
        conn.setAutoCommit( false );

        try {
            // MySQL
            // String sql = "INSERT INTO t_staff ( id , name , gender , hiredate ) VALUES ( 1 , '姜禹志' , 'male' , '2021-12-31' )" ;
            // Oracle
            String sql = "INSERT INTO t_staff ( id , name , gender , hiredate ) VALUES ( 1 , '姜禹志' , 'male' , to_date('2021-12-31' , 'yyyy-mm-dd' ))";
            // 发送SQL给数据库服务器、获取数据库服务器执行结果
            int count = st.executeUpdate(sql);
            System.out.println("受当前DML语句影响的记录数: " + count);
            // 事务处理第二步: 如果执行成功则提交事务
            conn.commit(); // 通过 Connection 来提交事务
        } catch ( SQLException e ) {
            // 事务处理第二步: 如果执行失败则回滚事务
            conn.rollback(); // 通过 Connection 来回滚事务
            System.out.println( "执行DML语句时发生错误: " + e.getMessage() );
        }

        // 事务处理第三步: 重新启用自动提交
        conn.setAutoCommit( true );

        // 释放资源
        st.close();
        conn.close();

    }
}
